/**
 * 
 */
package infocluster.task;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Hashtable;

import jxl.*;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import infocluster.prototype.ExcelProcessTask;
import infocluster.prototype.IExcelProcessTask;
import infocluster.prototype.Merchant;

/**
 * @author 08302010017 Xi Qi
 *
 */
public class OfferlistUpdateExcelProcessTask extends ExcelProcessTask
		implements IExcelProcessTask {
	
	public static final int MERCHANT_ENGLISH = 0;
	public static final int MERCHANT_CHINESE = 1;
	
	public static final WritableFont NORMAL_FONT = new  WritableFont(WritableFont.TIMES, 10 ,WritableFont.NO_BOLD);
	public static final WritableFont BOLD_FONT = new  WritableFont(WritableFont.TIMES, 10 ,WritableFont.BOLD);
	
	//private static final String TEMP_OFFER_LIST = "tmp/HJBB_temp_offerlist.xls"; 
	private static final int COLUMN_OFFER = 18;
	private static final int COLUMN_MERCHANT = 1;
	private static final int COLUMN_OUTLET = 11;
		
	private static final Hashtable<String,String> allOutlets = new Hashtable<String, String>();
	private static final Hashtable<String, ArrayList<String>> offerOutlets = new Hashtable<String, ArrayList<String>>();

	/* (non-Javadoc)
	 * @see infocluster.prototype.IExcelProcessTask#execute()
	 */
	public boolean execute() throws IOException{
		
		Hashtable<String,String[]> merchants = new Hashtable<String, String[]>();
		
		try {
			Workbook offerWb = Workbook.getWorkbook(this.getSource(0));
			Workbook merchantWb = Workbook.getWorkbook(this.getSource(1));
			WritableWorkbook newOfferWb = Workbook.createWorkbook(this.getDest(0));
			
			Sheet merchantSheet = merchantWb.getSheet(0);
			int mRowCount = merchantSheet.getRows();
			
			// build existing merchant list
			for(int i=1;i<mRowCount;i++){
				String merchantEName = merchantSheet.getCell(Merchant.ENAME, i).getContents().trim().replace("\n", "");
				String merchantCName = merchantSheet.getCell(Merchant.CNAME, i).getContents().trim().replace("\n", "");
				if(!merchantEName.equals("") && !merchantCName.equals("") && merchants.get(merchantCName) == null){
					merchants.put(merchantCName, new String[]{merchantCName, merchantEName});
				}
				
				String[] otherCName = merchantSheet.getCell(Merchant.ONAME, i).getContents().trim().replace("\n","").split(",");
				if(otherCName.length != 0){
					for(int j=0;j<otherCName.length;j++){
						if(!merchantEName.equals("") && !merchantCName.equals("") && !otherCName[j].equals("") && merchants.get(otherCName[j]) == null){
							merchants.put(otherCName[j], new String[]{merchantCName, merchantEName});
						}
					}
				}
			}
			
			Sheet offerSheet = offerWb.getSheet(0);
			int rowCount = offerSheet.getRows();
			int columnCount = offerSheet.getColumns();
			
			WritableSheet newOfferSheet = newOfferWb.createSheet("Offer List", 0);
			
			int i = 0;
			
			// print the column headers
			for(;i<2;i++){
				for(int j=0;j<columnCount;j++){
					Cell c = offerSheet.getCell(j,i);
					WritableCellFormat cf = new WritableCellFormat(c.getCellFormat()); 
					cf.setFont(BOLD_FONT);
					String content = c.getContents();
					
					Label label = null;
						newOfferSheet.setColumnView(j, offerSheet.getColumnView(j));
						label = new Label(j, i, content);			
					label.setCellFormat(cf);
					newOfferSheet.addCell(label); 
				}
			}
			
			for(;i<rowCount;i++){
				String merchantEName = null, merchantSCName = null;
				String merchantCName = offerSheet.getCell(1,i).getContents().trim();
				String[] merchantNamePair = merchants.get(merchantCName);
				if(merchantNamePair != null){
					merchantEName = merchantNamePair[1];
					merchantSCName = merchantNamePair[0];
				}				
				for(int j=0;j<columnCount;j++){					
					Cell c = offerSheet.getCell(j,i);
					WritableCellFormat cf = new WritableCellFormat(c.getCellFormat());					
					cf.setFont(NORMAL_FONT);
					String content = c.getContents();
					content = content.trim();
					if(merchantEName != null){
						if(j == 0)
							content = merchantEName;
						if(j == 1)
							content = merchantSCName;
					}else{
						if(j >= 0 && j <= 15){
							cf.setBackground(Colour.YELLOW);
						}						
					}
					
					Label label =  new Label(j, i, content);
					label.setCellFormat(cf);
					newOfferSheet.addCell(label); 
				}
			}
			
			buildAllOutletList(merchantSheet, mRowCount);
			
			for(i=2;i<rowCount;i++){
				String offer = newOfferSheet.getCell(COLUMN_OFFER , i).getContents().trim();
				String merchant = newOfferSheet.getCell(COLUMN_MERCHANT , i).getContents().trim();
				String outlet = newOfferSheet.getCell(COLUMN_OUTLET , i).getContents().trim();
				String key = merchant + ":" + offer;
				
				ArrayList<String> outlets = new ArrayList<String>();
				if((outlets = offerOutlets.get(key)) == null){
					outlets = new ArrayList<String>();
					outlets.add(outlet);
					offerOutlets.put(key, outlets);
				}else{
					if(!outlets.contains(outlet)){
						outlets.add(outlet);
					}					
				}
			}			
			
			for(i=2;i<rowCount;i++){
				updateOfferDetail(newOfferSheet, merchantSheet, i);
			}
			
			newOfferWb.write(); 
			newOfferWb.close();
			
			offerWb.close();
			merchantWb.close();
			return true;
			
		} catch (ArrayIndexOutOfBoundsException e) {
			e.printStackTrace();
			return false;
		} catch (BiffException e) {
			e.printStackTrace();
			return false;
		} catch (RowsExceededException e) {
			e.printStackTrace();
			return false;
		} catch (WriteException e) {
			e.printStackTrace();
			return false;
		}
	}


	/**
	 * @param mRowCount 
	 * 
	 */
	private void buildAllOutletList(Sheet merchantSheet, int mRowCount) {
		for(int i=1;i<mRowCount;i++){
			String merchant = merchantSheet.getCell(1,i).getContents().trim();
			String aostr = merchantSheet.getCell(3,i).getContents().trim();
			allOutlets.put(merchant,aostr);
		}
	}

	/**
	 * @param newSheet
	 * @param i
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	private void updateOfferDetail(WritableSheet newSheet, Sheet merchantSheet, int i) throws RowsExceededException, WriteException {
		String merchant = newSheet.getCell(1, i).getContents().trim();
		String offer = newSheet.getCell(18,i).getContents().trim();
		
		ArrayList<String> outlets = offerOutlets.get(merchant+":"+offer);		
		if(outlets != null){
			String outletStr = join(outlets);
			String allOutletStr = allOutlets.get(merchant);
			
			System.out.println(allOutletStr);
			String content = newSheet.getCell(20, i).getContents().trim();
			
			if(allOutletStr != null){
				String[] allOutlet = allOutletStr.split("、");
				if(!outletStr.equals("")){
					if(!cover(outlets, allOutlet)){
						content = "限"+outletStr+"使用，"+content;
					}
				}
			}			
			
			Label label = new Label(20, i, content);
			
			WritableCellFormat cf = new WritableCellFormat(label.getCellFormat());
			if(allOutletStr == null){				
				cf.setBackground(Colour.GREEN);
			}else{
				if(allOutletStr.equals("")){
					cf.setBackground(Colour.ORANGE);
				}
				if(outletStr.equals("")){
					cf.setBackground(Colour.YELLOW);
				}
			}			
			
			label.setCellFormat(cf);
			
			newSheet.addCell(label);
		}
	}

	/**
	 * @param outlets
	 * @param allOutlet
	 * @return
	 */
	private boolean cover(ArrayList<String> outlets, String[] allOutlet) {
		for(int i=0;i<allOutlet.length;i++){
			if(!outlets.contains(allOutlet[i])){
				return false;
			}
		}
		return true;
	}


	/**
	 * @param outlets
	 * @return
	 */
	private String join(ArrayList<String> outlets) {
		String temp = "";
		for(int i=0;i<outlets.size();i++){
			if(i != 0) temp += "、";
			temp += outlets.get(i);
		}
		
		return temp;
	}
}
